package com.xingtai.db;


import java.io.InputStream;
import java.io.Serializable;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;

import com.xingtai.util.Utilities;

abstract class DBBaseBean implements Serializable {
	
	private static final long serialVersionUID = 1L;
	
	private Logger logger = Logger.getLogger(DBBaseBean.class);
	
	public DBBaseBean() {

	}
    
    /**
     * Execute SQL Query. Return a list of rows in the result set. Each row is structured
     * as a hashtable. use 'get(columnName)' to retrieve the according result.
     * @throws java.sql.SQLException
     * @return 
     * @param sql
     */
      
    protected List getResult(String sql) throws SQLException{
    	logger.debug(sql);
        PreparedStatement stmt = null;
        Connection conn = null;
        ResultSet rs = null;
        List rst = new ArrayList();
        
        try{            
            conn = (new ConnectionManager()).getConnection();
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if (columnCount == 0)
                return null;
                        
            while(rs.next()) {
            	Hashtable row = new Hashtable();
                Object value = null;
                for(int i = 1; i <= columnCount; i ++){
                	value = new String("");
                    if(rsmd.getColumnTypeName(i).equalsIgnoreCase("BLOB")){ 		//判断列是否为Blob型          	    	
           	    		Blob lob = rs.getBlob(i);   //取得blob值
           	    		value = blobToString(lob);        	    		//blob值转成String
           	    	}else if(rsmd.getColumnTypeName(i).equalsIgnoreCase("VARBINARY")){
           	    		if(rs.getBytes(i) != null)  value = new String(rs.getBytes(i));
           	    	}else{
                        if(rs.getString(i) != null) value = new String(rs.getString(i));                              
                	}
                    if(value == null)   value = new String("");
                    row.put(rsmd.getColumnLabel(i).toUpperCase(), value);
                }
                rst.add(row);
            }
        }finally{
            if(rs != null)    rs.close();
            if(stmt != null)  stmt.close();
            if(conn != null)  conn.close();
            
        }
        return rst;
    }
    
         
    /**
     * Execute SQL update/insert/delete statement. Use this method when you have one
     * update to execute, otherwise, please use 'batchUpdate' method.
     * @throws java.sql.SQLException
     * @return 
     * @param sql   
     */
    protected void excuteUpdate(String sql) throws SQLException{
    	logger.debug(sql);
        Connection conn = null; 
        Statement stmt = null;
        
        try{            
            conn = (new ConnectionManager()).getConnection();
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
            conn.commit();
        }catch(SQLException ex){
            conn.rollback();
            throw ex;
        }finally{
            if(stmt != null)  stmt.close();
            if(conn != null)  conn.close();
        }        
    }
    
    /**
     * This method will execute all updates passed in a whole, which means, transaction
     * is been considered
     * 
     * @throws java.sql.SQLException
     * @return 
     * @param sql
     */
    protected void batchUpdate(List sqlList) throws SQLException{        
        Connection conn = null;
        Statement ps  =	null;
        String currstmt = "";
        
        try{            
			conn = (new ConnectionManager()).getConnection();
            conn.setAutoCommit(false);
            ps = conn.createStatement();
            
            for(Iterator iter = sqlList.iterator(); iter.hasNext();){
                currstmt = iter.next().toString();
                logger.debug(currstmt);
                ps.executeUpdate(currstmt);
            }
            
            conn.commit();
        }catch(SQLException ex){
            logger.error("事务性更新发生错误\nsql["+currstmt+"]", ex);
            conn.rollback();
            ex.printStackTrace();
            throw ex;
        }finally{
            if(ps != null)  ps.close();            
            if(conn!=null)   conn.close();
        }
    }
    
       
    private static String blobToString(Blob lob){
		StringBuffer value = new StringBuffer();
		Blob blob = lob;
		try {
			InputStream blobStream = blob.getBinaryStream();
			int   nbytes = 0; // Number of bytes read           	    		           	    		
			byte  buffer[] = new byte[1024];
			while((nbytes = blobStream.read(buffer)) != -1) {	
				String tmp = Utilities.ByteArraytoHexString(buffer, nbytes);
				value.append(tmp); 
			}
		}catch(Exception e){
            e.printStackTrace();
			return value.toString();
		}        
		return value.toString();		
	}
    
    protected void updateKeyBytes(String sql,String key, byte[] bytes ) throws SQLException{
    	logger.debug(sql);
        PreparedStatement ps  =	null;
        Connection conn = null;
        try{
            conn = (new ConnectionManager()).getConnection();
            conn.setAutoCommit(false);
			ps =conn.prepareStatement(sql);
            ps.setString(1,key);
            ps.setBytes(2, bytes);           
			ps.executeBatch();
            conn.commit();
        }catch(SQLException ex){
            ex.printStackTrace();
            conn.rollback();
            throw ex;
            
        }finally{
            if(ps != null)  ps.close();
            if(conn!=null)  conn.close();
        }
    }
    
}
